const router = require('koa-router')()

const jwt = require('jsonwebtoken');
const encryption = require("../utils/encryption");
const format = require("../utils/format");
const excel = require("../utils/excel");
const XLSX = require('xlsx');
const xlsx = require('node-xlsx');
const XlsxPopulate = require('xlsx-populate');
const fs = require("fs");
const crypto = require('crypto');
const path = require("path");
let formidable = require('formidable');

router.get('/', async (ctx, next) => {
  await ctx.render('index', {
    title: 'Hello Koa 2!'
  })
})

router.post('/uploadQr', async(ctx, next) => {
  const form = formidable.formidable({ uploadDir: path.join(__dirname, '../codeupload'),  keepExtensions: true, maxFileSize: 200 * 1024 * 1024 });
  const [fields, files] = await form.parse(ctx.req);
  let result = '';
  for(let i = 0; i < files.file.length; i++) {
    let newPath = path.join(__dirname, '../codeupload', files.file[i].originalFilename);
    let oldPath = files.file[i]['filepath'];
    fs.rename(oldPath, newPath, (err) => {
      if (err) throw err;
    });
    let fileName = files.file[i].originalFilename.slice(0, -4);
    let buf = fs.readFileSync(files.file[i]['filepath']);
    buf = buf.toString("base64");
    buf = 'data:image/png;base64,' + buf;
    let insertSql = 'insert into classinfor (studentClass, codeqr) values (?, ?)';
    result = await ctx.db.EXECUTE(insertSql, fileName, buf);
  }
  if(result.affectedRows > 0) {
    ctx.body = {
      status: 'ok'
    }
  }
})

router.post('/getinfor', async (ctx, next) => {
  let { studentClass, studentId } = ctx.request.body;
  let selectSql = "select * from classinfor where studentClass=?";
  let result = await ctx.db.EXECUTE(selectSql, studentClass);
  if(result.length > 0) {
    ctx.body = {
      status: 'ok',
      result: result[0]
    }
  }
})


router.post('/upload', async (ctx,next) => {
  const form = formidable.formidable({ uploadDir: path.join(__dirname, '../upload'),  keepExtensions: true, maxFileSize: 200 * 1024 * 1024 });
  const [fields, files] = await form.parse(ctx.req);
  if(files.file.length > 0) {
    XlsxPopulate.fromFileAsync(files.file[0].filepath)
        .then(async workbook => {
          // Modify the workbook.
          const value = workbook.sheet("Sheet1").cell("A1").value();
          let sheet1 = workbook.sheet(0);
          let len = sheet1.usedRange()._maxRowNumber;
          console.log(sheet1.usedRange());
          let arr = [];
          for(let i = 2; i < len + 1; i++) {
            let insertStr = "(";
            for(let j = 1; j < 7; j++) {
              let str = sheet1.row(i).cell(j).value();
              str = (str == undefined ? '' : str);
              insertStr += "'" + str + "'" + ','
            }
            insertStr = insertStr.slice(0, -1) + ")";
            arr.push(insertStr);
          }
          
          let values = arr.join(",");
          
          const sql = `INSERT INTO student (studentName, idNum, studentClass, studentMaster, masterPhone, phoneNum) VALUES ${values}`;
          let result = await ctx.db.EXECUTE(sql);
          if(result.affectedRows > 0) {
            ctx.body = {
              status: 'ok'
            }
          }
        });
        ctx.body = {
          status: 'ok'
        }
  }
  
})


router.post('/login', async (ctx, next) => {
  let password = ctx.request.body.pwd;
  let userName = ctx.request.body.userName;
  let md5 = crypto.createHash("md5");
  let newPas = md5.update(password).digest("hex");
  let sql = "select * from loginuser where userName=? and pwd=? ";
  let result = await ctx.db.EXECUTE(sql, userName, newPas);
  if (result.length > 0) {
    let token = jwt.sign({ username: userName }, 'shhhhh');
    let timeout = (new Date()).getTime() + 2*60*60*1000;
    timeout = format.formatDateTime(new Date(timeout));
    let tokenSql = "update loginuser set token=?, timeout=? where userName=? and pwd=?";
    let tokenResult = await ctx.db.EXECUTE(tokenSql, token, timeout, userName, newPas);
    ctx.body = {
      msg: "success",
      token: token
    }
  }
})

router.post('/minlogin', async (ctx, next) => {
  let idNum = ctx.request.body.idNum;
  let phone = ctx.request.body.phone;
  let sql = "select * from student where idNum=? and studentName=? ";
  let result = await ctx.db.EXECUTE(sql, idNum, phone);
  if(result.length > 0) {
    let token = jwt.sign({ username: idNum }, 'shhhhh');
    let timeout = (new Date()).getTime() + 2*60*60*1000;
    timeout = format.formatDateTime(new Date(timeout));
    let tokenSql = "update student set token=?, timeout=? where idNum=? and studentName=?";
    let tokenResult = await ctx.db.EXECUTE(tokenSql, token, timeout, idNum, phone);
    if (result.length > 0) {
      ctx.body = {
        msg: "success",
        result: result[0],
        token
      }
    }
  }
  
})

router.get("/user", async (ctx, next) => {
  let page = parseInt(ctx.query.page);
  let size = parseInt(ctx.query.size);
  let userName = ctx.query.user;
  let countSql = "SELECT COUNT(*) AS total FROM student ;"
  let selcectSql = "select studentId, studentName, idNum, studentClass, studentMaster, masterPhone, phoneNum from student ORDER BY studentId DESC LIMIT ? OFFSET ?;";
  let countResult = await ctx.db.EXECUTE(countSql);
  let result = await ctx.db.EXECUTE(selcectSql, size, size * (page - 1));

  ctx.body = {
    status: "ok",
    userList: result,
    total: countResult[0]["total"]
  }
})

router.get("/parent", async (ctx, next) => {
  let page = parseInt(ctx.query.page);
  let size = parseInt(ctx.query.size);
  let userName = ctx.query.user;
  let countSql = "SELECT COUNT(*) AS total FROM parentinfor ;"
  let selcectSql = "select parentinfor.studentId, studentName, parentName1, parentinfor.idNum1, parentinfor.phoneNum1, workUnit1, parentinfor.address1, parentName2, parentinfor.idNum2, parentinfor.phoneNum2, workUnit2, parentinfor.address2, familyNum from parentinfor left join student on parentinfor.studentId=student.studentId  ORDER BY parentId DESC LIMIT ? OFFSET ?;";
  let countResult = await ctx.db.EXECUTE(countSql);
  let result = await ctx.db.EXECUTE(selcectSql, size, size * (page - 1));

  ctx.body = {
    status: "ok",
    userList: result,
    total: countResult[0]["total"]
  }
})



router.post("/minparent", async (ctx, next) => {
  let { studentId, parentName1, parentIdNum1, phoneNum1, workUnit1, address1, parentName2, parentIdNum2, phoneNum2, workUnit2, address2, familyNum} = ctx.request.body;
  
  let selectSql = "select * from parentinfor where studentId=?";
  let selectResult = await ctx.db.EXECUTE(selectSql, studentId);
  if(selectResult.length > 0) {
      let updateSql = "update parentinfor set parentName1=?, idNum1=?, phoneNum1=?, workUnit1=?, address1=?,  parentName2=?, IdNum2=?, phoneNum2=?, workUnit2=?, address2=?, familyNum=? where studentId=?";
      let updateResult = await ctx.db.EXECUTE(updateSql, parentName1, parentIdNum1, phoneNum1, workUnit1, address1, parentName2, parentIdNum2, phoneNum2, workUnit2, address2, familyNum, studentId);
      if(updateResult.affectedRows > 0) {
        ctx.body = {
          status: 'update ok'
        }
      }
  }
  else {
      let insertSql = "insert into parentinfor (parentName1, idNum1, phoneNum1, workUnit1, address1,  parentName2, IdNum2, phoneNum2, workUnit2, address2, familyNum,  studentId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
      let insertResult = await ctx.db.EXECUTE(insertSql, parentName1, parentIdNum1, phoneNum1, workUnit1, address1, parentName2, parentIdNum2, phoneNum2, workUnit2, address2, familyNum,  studentId);
      if(insertResult.affectedRows > 0) {
        ctx.body = {
          status: 'ok'
        }
      }
  }
  
})

router.post("/parent", async (ctx, next) => {
  let { studentName, idNum, parentName, parentIdNum, phoneNum, workUnit, address} = ctx.request.body;

  let selectSql = "select studentId from student where idNum=?";
  let result = await ctx.db.EXECUTE(selectSql, idNum);
  let studentId= '';
  if(result.length > 0) {
    studentId = result[0].studentId;
  }
  let insertSql = "insert into parentinfor (parentName, idNum, phoneNum, workUnit, address, studentId) values  (?, ?, ?, ?, ?, ?)";
  let insertResult =await ctx.db.EXECUTE(insertSql, parentName, parentIdNum, phoneNum, workUnit, address, studentId);
  if(insertResult.affectedRows > 0) {
    ctx.body = {
      status: 'ok'
    }
  }
})

//小程序提交信息
router.post("/subinfor", async (ctx, next) => {
  let reqBody = ctx.request.body;
  let { userName, cardId, address, phone, subdistrict, building, block, room, deviceId } = reqBody;
  //userName = encryption.encrypt(userName);
  cardId = encryption.encrypt(cardId);
  address = encryption.encrypt(address);
  phone = encryption.encrypt(phone);
  subdistrict = encryption.encrypt(subdistrict);
  building = encryption.encrypt(building);
  block = encryption.encrypt(block);
  room = encryption.encrypt(room);
  let timer = format.formatDateTime(new Date());
  if (deviceId) {
    let insertSql = "insert into information (user_name, card_id, address, phone, subdistrict, building, block, room, timer) values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
    let result = await ctx.db.EXECUTE(insertSql, userName, cardId, address, phone, subdistrict, building, block, room, timer);
    if (result.affectedRows == 1) {
      ctx.body = { "status": "ok" };
    }
  }
})

//信息更新
router.post('/user', async (ctx, next) => {
  let updateBody = ctx.request.body;
  let { studentName, idNum, studentClass, studentMaster, masterPhone, phoneNum, studentId } = updateBody;
  let updateSql = "update student set studentName=?, idNum=?, studentClass=?, studentMaster=?, masterPhone=?, phoneNum=? where studentId=?";
  let result = await ctx.db.EXECUTE(updateSql, studentName, idNum, studentClass, studentMaster, masterPhone, phoneNum, studentId, studentId);
  ctx.body = {
    status: 'ok'
  }
})

router.post('/adduser', async (ctx, next) => {
  let addBody = ctx.request.body;
  let { studentName, idNum, studentClass, studentMaster, masterPhone, phoneNum, studentId } = addBody;
  let updateSql = "insert into student (studentName, idNum, studentClass, studentMaster, masterPhone, phoneNum) values (?, ?, ?, ?, ?, ?)";
  let result = await ctx.db.EXECUTE(updateSql, studentName, idNum, studentClass, studentMaster, masterPhone, phoneNum);
  if(result.affectedRows > 0) {
    ctx.body = {
      status: 'ok'
    }
  }
})


router.post("/search", async (ctx, next) => {
  let userName = ctx.request.body.search;
  userName = `%${userName}%`;
  let searchSql = `SELECT * FROM student WHERE studentName LIKE ?`;
  let result = await ctx.db.EXECUTE(searchSql, userName);
  ctx.body = {
    status: "ok",
    userList: result
  }
})

router.post("/searchParent", async (ctx, next) => {
  let studentName = ctx.request.body.search;
  studentName = `%${studentName}%`;
  let searchSql = `SELECT * FROM  parentinfor left join student on parentinfor.studentId=student.studentId WHERE  studentName LIKE ?`;
  let result = await ctx.db.EXECUTE(searchSql, studentName);
  ctx.body = {
    status: "ok",
    userList: result
  }
})


router.get('/export', async (ctx) => {
  let userName = ctx.query.user;
  let matchList = {'studentName': '学生姓名', 'parentName1': '监护人1姓名', 'idNum1': '监护人1身份证号', 'phoneNum1': '监护人1电话', 'workUnit1': '监护人1工作单位', 'address1': '监护人1地址', 'parentName2': '监护人2姓名', 'idNum2': '监护人2身份证号', 'phoneNum2': '监护人2电话号码', 'workUnit2': '监护人2工作单位', 'address2': '监护人2地址', 'familyNum': '亲情号'}
  let selcectSql = "select studentName, parentName1, parentinfor.idNum1, parentinfor.phoneNum1, workUnit1, parentinfor.address1, parentName2, parentinfor.idNum2, parentinfor.phoneNum2, workUnit2, parentinfor.address2, familyNum from parentinfor left join student on parentinfor.studentId=student.studentId  ORDER BY parentId DESC";
  let result = await ctx.db.EXECUTE(selcectSql);
  let decryptResult = [{
    '学生姓名': '学生姓名',
    '监护人1姓名': '监护人1姓名',
    '监护人1身份证号': '监护人1身份证号',
    '监护人1电话': '监护人1电话',
    '监护人1工作单位': '监护人1工作单位',
    '监护人1地址': '监护人1地址',
    '监护人2姓名': '监护人1姓名',
    '监护人2身份证号': '监护人1身份证号',
    '监护人2电话': '监护人1电话',
    '监护人2工作单位': '监护人1工作单位',
    '监护人2地址': '监护人1地址',
    '亲情号': '亲情号'
  }];
  if (result.length > 0) {
    for (let i = 0; i < result.length; i++) {
      let newInfor = {};
      for (let j in result[i]) {
        newInfor[matchList[j]] = result[i][j];
      }
      decryptResult.push(newInfor);
    }
  }
  let excelData = await excel.generateExcelBuffer(decryptResult);
  ctx.set('Content-Type', 'application/octet-stream');
  ctx.body = excelData;
});

module.exports = router
